package org.jeecg.modules.pacs.mapperPacs;

import com.baomidou.dynamic.datasource.annotation.DS;
import org.jeecg.modules.pacs.entity.HealthCard;
import org.jeecg.modules.pacs.entity.ReportId;
import org.jeecg.modules.pacs.entity.ReportTable;
import org.apache.ibatis.annotations.Select;

import java.util.List;


public interface FindPacsReportDao {
    /**
     * 通过身份证号和时间查询报告单
     * @param healthCard
     * @return
     */
    @Select("select\n"
            + "RIS.CACCNO AS reportId,"
            + " '01' AS type,"
            + "RIS.DCHECKDATE AS submissionTime,"
            + "RIS.CNAME as patientName,"
            + "RIS.CAGE AS age,"
            + "case when RIS.CSEX='男' then '1' else '2' end as sex,"
            + "RGS.CIDCARD AS cardNo,"
            + " ris.cblkh  as hosUserNo,"
            + " ris.cjsxm as   checker,"
            +"RIS.CMODALITY as checkType,\n"
            + " RIS.CBZ as  checkSite,\n"
            + " RIS.CBZ  as  checkMethod ,\n"
            + " RIS.CBZ  as  checkName ,\n"
            + " RISBG.DBGSJ AS reportTime,"
            + " RISBG.CBGYSXM AS doctorName,"
            + " RISBG.CSHYSXM AS auditor,"
            + " RISBG.CBGZD AS result,"
            + " RISBG.CBZ as remark,"
            + " risbg.cbgzt as status,"
            + " '放射科' as departName "
            + ",RISBG.CBGZD		as	words FROM eris.eris_EXAM RIS, eris.eris_EXAM_BG RISBG, eris.eris_REGISTER RGS\n"
            + "WHERE RIS.IID = RISBG.IEXAM_IID AND\n"
            + "ris.iid=rgs.iid and\n"
            + "RISBG.CFLAG = '9'\n"
            + "and risbg.cbgzt='已审核'\n"
            + "and RGS.CIDCARD= #{idCardNo}\n"
            + " and RIS.DBGSJ > TO_DATE(#{startTime}, 'yyyy-mm-dd HH24:MI:SS') \n"
            + "and RISBG.DBGSJ < TO_DATE(#{endTime}, 'yyyy-mm-dd HH24:MI:SS')  "
            + "   union all\n"
            + "select\n"
            + "RIS.CACCNO AS reportId,\n"
            + "'01' AS type,  \n"
            + "RIS.DCHECKDATE AS submissionTime,\n"
            + "RIS.CNAME as patientName, \n"
            + "RIS.CAGE AS age,\n"
            + "case when RIS.CSEX='男' then '1' else '2' end as sex,"
            + "RGS.CIDCARD AS cardNo,\n"
            + " ris.cblkh  as hosUserNo, \n"
            + " ris.cjsxm as    checker, \n"
            +"RIS.CMODALITY as checkType,\n"
            + " RIS.CBZ as  checkSite,\n"
            + " RIS.CBZ  as  checkMethod ,\n"
            + " RIS.CBZ  as  checkName ,\n"
            + " RISBG.DBGSJ AS reportTime,\n"
            + " RISBG.CBGYSXM AS doctorName,\n"
            + " RISBG.CSHYSXM AS auditor,\n"
            + " RISBG.CBGZD AS result,\n"
            + " RISBG.CDYBZ as remark,\n"
            + " risbg.cbgzt as status,\n"
            + " 'B超室' as departName\n"
            + " ,RISBG.CBGZD		as	words FROM eris.eris_uis_EXAM RIS, eris.eris_uis_EXAM_BG RISBG, eris.eris_REGISTER RGS\n"
            + "WHERE RIS.IID = RISBG.IEXAM_IID AND\n"
            + "ris.iid=rgs.iid and\n"
            + "RISBG.CFLAG = '9'\n"
            + "and risbg.cbgzt='已审核'\n"
            + "and RGS.CIDCARD= #{idCardNo}"
            + " and RIS.DBGSJ > TO_DATE(#{startTime}, 'yyyy-mm-dd HH24:MI:SS') \n"
            + "and RISBG.DBGSJ < TO_DATE(#{endTime}, 'yyyy-mm-dd HH24:MI:SS')  ")
    List<ReportTable> findRepotrTableByIdcardAndTime(HealthCard healthCard);
    /**
     * 通过电子健康卡号和时间查询报告单
     * @param healthCard
     * @return
     */
    @Select("select\n"
            + "RIS.CACCNO AS reportId,"
            + " '01' AS type,"
            + "RIS.DCHECKDATE AS submissionTime,"
            + "RIS.CNAME as patientName,"
            + "RIS.CAGE AS age,"
            + "case when RIS.CSEX='男' then '1' else '2' end as sex,"
            + "RGS.CIDCARD AS cardNo,"
            + " ris.cblkh  as hosUserNo,"
            + " ris.cjsxm as   checker,"
            +"RIS.CMODALITY as checkType,\n"
            + " RIS.CBZ as  checkSite,\n"
            + " RIS.CBZ  as  checkMethod ,"
            + " RIS.CBZ  as  checkName ,\n"
            + " RISBG.DBGSJ AS reportTime,"
            + " RISBG.CBGYSXM AS doctorName,"
            + " RISBG.CSHYSXM AS auditor,"
            + " RISBG.CBGZD AS result,"
            + " RISBG.CBZ as remark,"
            + " risbg.cbgzt as status,"
            + " '放射科' as departName "
            + ",RISBG.CBGZD		as	words FROM eris.eris_EXAM RIS, eris.eris_EXAM_BG RISBG, eris.eris_REGISTER RGS\n"
            + "WHERE RIS.IID = RISBG.IEXAM_IID AND\n"
            + "ris.iid=rgs.iid and\n"
            + "RISBG.CFLAG = '9'\n"
            + "and risbg.cbgzt='已审核'\n"
            + "and RIS.CQTH= #{cardNo}\n"
            + " and RIS.DBGSJ > TO_DATE(#{startTime}, 'yyyy-mm-dd HH24:MI:SS') \n"
            + "and RISBG.DBGSJ < TO_DATE(#{endTime}, 'yyyy-mm-dd HH24:MI:SS')  "
            + "   union all\n"
            + "select\n"
            + "RIS.CACCNO AS reportId,\n"
            + "'01' AS type,  \n"
            + "RIS.DCHECKDATE AS submissionTime,\n"
            + "RIS.CNAME as patientName, \n"
            + "RIS.CAGE AS age,\n"
            + "case when RIS.CSEX='男' then '1' else '2' end as sex,"
            + "RGS.CIDCARD AS cardNo,\n"
            + " ris.cblkh  as hosUserNo, \n"
            + " ris.cjsxm as    checker, \n"
            +" RIS.CMODALITY as checkType,\n"
            + " RIS.CBZ as  checkSite,\n"
            + " RIS.CBZ  as  checkMethod ,"
            + " RIS.CBZ  as  checkName ,\n"
            + " RISBG.DBGSJ AS reportTime,\n"
            + " RISBG.CBGYSXM AS doctorName,\n"
            + " RISBG.CSHYSXM AS auditor,\n"
            + " RISBG.CBGZD AS result,\n"
            + " RISBG.CDYBZ as remark,\n"
            + " risbg.cbgzt as status,\n"
            + " 'B超室' as departName\n"
            + " ,RISBG.CBGZD		as	words FROM eris.eris_uis_EXAM RIS, eris.eris_uis_EXAM_BG RISBG, eris.eris_REGISTER RGS\n"
            + "WHERE RIS.IID = RISBG.IEXAM_IID AND\n"
            + "ris.iid=rgs.iid and\n"
            + "RISBG.CFLAG = '9'\n"
            + "and risbg.cbgzt='已审核'\n"
            + "and RIS.CQTH= #{cardNo}"
            + " and RIS.DBGSJ > TO_DATE(#{startTime}, 'yyyy-mm-dd HH24:MI:SS') \n"
            + "and RISBG.DBGSJ < TO_DATE(#{endTime}, 'yyyy-mm-dd HH24:MI:SS')  ")
    List<ReportTable> findRepotrTableByHealthCardAndTime(HealthCard healthCard);
    /**
     * 通过报告单ID和类型查询报告单
     * @param reportId
     * @return
     */
    @Select("select\n"
            + "RIS.CACCNO AS reportId,"
            + " '01' AS type,"
            + "RIS.DCHECKDATE AS submissionTime,"
            + "RIS.CNAME as patientName,"
            + "RIS.CAGE AS age,"
            + "case when RIS.CSEX='男' then '1' else '2' end as sex,"
            + "RGS.CIDCARD AS cardNo,"
            + " ris.cblkh  as hosUserNo,"
            + " ris.cjsxm as   checker,"
            +"RIS.CMODALITY as checkType,\n"
            + " RIS.CBZ as  checkSite,\n"
            + " RIS.CBZ  as  checkMethod ,"
            + " RIS.CBZ  as  checkName ,"
            + " RISBG.DBGSJ AS reportTime,"
            + " RISBG.CBGYSXM AS doctorName,"
            + " RISBG.CSHYSXM AS auditor,"
            + " RISBG.CBGZD AS result,"
            + " RISBG.CBZ as remark,"
            + " risbg.cbgzt as status,"
            + " '放射科' as departName "
            + ",RISBG.CBGZD		as	words FROM eris.eris_EXAM RIS, eris.eris_EXAM_BG RISBG, eris.eris_REGISTER RGS\n"
            + "WHERE RIS.IID = RISBG.IEXAM_IID AND\n"
            + "ris.iid=rgs.iid and\n"
            + "RISBG.CFLAG = '9'\n"
            + "and risbg.cbgzt='已审核'\n"
            + "and '01'= #{type}\n"
            + " and RIS.CACCNO= #{reportId} \n"
            + "   union all\n"
            + "select\n"
            + "RIS.CACCNO AS reportId,\n"
            + "'01'  AS type,  \n"
            + "RIS.DCHECKDATE AS submissionTime,\n"
            + "RIS.CNAME as patientName, \n"
            + "RIS.CAGE AS age,\n"
            + "case when RIS.CSEX='男' then '1' else '2' end as sex,\n"
            + "RGS.CIDCARD AS cardNo,\n"
            + " ris.cblkh  as hosUserNo, \n"
            + " ris.cjsxm as    checker, \n"
            +" RIS.CMODALITY as checkType,\n"
            + " RIS.CBZ as  checkSite,\n"
            + " RIS.CBZ  as  checkMethod ,\n"
            + " RIS.CBZ  as  checkName ,\n"
            + " RISBG.DBGSJ AS reportTime,\n"
            + " RISBG.CBGYSXM AS doctorName,\n"
            + " RISBG.CSHYSXM AS auditor,\n"
            + " RISBG.CBGZD AS result,\n"
            + " RISBG.CDYBZ as remark,\n"
            + " risbg.cbgzt as status,\n"
            + " 'B超室' as departName\n"
            + " ,RISBG.CBGZD		as	words FROM eris.eris_uis_EXAM RIS, eris.eris_uis_EXAM_BG RISBG, eris.eris_REGISTER RGS\n"
            + "WHERE RIS.IID = RISBG.IEXAM_IID AND\n"
            + "ris.iid=rgs.iid and\n"
            + "RISBG.CFLAG = '9'\n"
            + "and risbg.cbgzt='已审核'\n"
            + "and RIS.CACCNO= #{reportId}"
            + " and  '01' = #{type}\n"
            + " ")
    ReportTable findRepotrTableByReportIdAndType(ReportId reportId);

    /**
     * 根据时间查询报告单
     *
     * @return
     */
    @Select("SELECT\n" +
            "RIS.CACCNO AS reportNo,\n"
            + "RIS.DCHECKDATE AS submissionTime,\n"
            + "RIS.CNAME AS patientName,\n"
            + "RIS.CAGE AS age,\n"
            + "RIS.CYQBM as type,\n"
            + "CASE\n"
            + "WHEN RIS.CSEX = '男' THEN\n"
            + "'1'\n"
            + "ELSE\n"
            + "'2'\n"
            + "END AS sex,\n"
            + " RGS.CIDCARD AS cardNo,\n"
            + " ris.cblkh AS hosUserNo,\n"
            + " RIS.CMODALITY AS checkType,\n"
            + " RIS.CBZ AS checkSite,\n"
            + " RIS.CBZ AS checkMethod,\n"
            + " RIS.CBZ  as  checkName ,\n"
            + " RISBG.DBGSJ AS reportTime,\n"
            + " RISBG.CBGYSXM AS doctorName,\n"
            + " RISBG.CSHYSXM AS auditor,\n"
            + " RISBG.CBGZD AS result,\n"
            + " '放射科' AS departName,\n"
            + " RIS.CQTH AS checkNo "
            + "\n"
            + "FROM\n"
            + "eris.eris_EXAM RIS,\n"
            + "eris.eris_EXAM_BG RISBG,\n"
            + "eris.eris_REGISTER RGS\n"
            + "WHERE\n"
            + "RIS.IID = RISBG.IEXAM_IID\n"
            + "AND ris.iid = rgs.iid\n"
            + "AND RISBG.CFLAG = '9'\n"
            + "AND risbg.cbgzt = '已审核'\n"
            + "AND RGS.CIDCARD is not null\n"
            + "AND RIS.DSHSJ >= TO_DATE (\n"
            + "#{startTime},\n"
            + "'yyyy-mm-dd HH24:MI:SS'\n"
            + ")\n"
            + "AND RIS.DSHSJ < TO_DATE (\n"
            + "#{endTime},\n"
            + "'yyyy-mm-dd HH24:MI:SS'\n"
            + ")\n"
            + "UNION ALL\n"
            + "SELECT\n"
            +"RIS.CACCNO AS reportNo,\n"
            + "RIS.DCHECKDATE AS submissionTime,\n"
            + "RIS.CNAME AS patientName,\n"
            + "RIS.CAGE AS age,\n"
            + "RIS.CYQBM as type,\n"
            + "CASE\n"
            + "WHEN RIS.CSEX = '男' THEN\n"
            + "'1'\n"
            + "ELSE\n"
            + "'2'\n"
            + "END AS sex,\n"
            + "RGS.CIDCARD AS cardNo,\n"
            + "ris.cblkh AS hosUserNo,\n"
            + "RIS.CMODALITY AS checkType,\n"
            + "RIS.CBZ AS checkSite,\n"
            + "RIS.CBZ AS checkMethod,\n"
            + " RIS.CBZ  as  checkName ,\n"
            + "RISBG.DBGSJ AS reportTime,\n"
            + "RISBG.CBGYSXM AS doctorName,\n"
            + "RISBG.CSHYSXM AS auditor,\n"
            + "RISBG.CBGZD AS result,\n"
            + "'B超室' AS departName,\n"
            + " RIS.CQTH AS checkNo "
            + "FROM\n"
            + "eris.eris_uis_EXAM RIS,\n"
            + "eris.eris_uis_EXAM_BG RISBG,\n"
            + "eris.eris_REGISTER RGS\n"
            + "WHERE\n"
            + "RIS.IID = RISBG.IEXAM_IID\n"
            + "AND ris.iid = rgs.iid\n"
            + "AND RISBG.CFLAG = '9'\n"
            + "AND risbg.cbgzt = '已审核'\n"
            + "AND RGS.CIDCARD is not null\n"
            + "AND RIS.DSHSJ >= TO_DATE (\n"
            + "#{startTime},\n"
            + "'yyyy-mm-dd HH24:MI:SS'\n"
            + ")"
            + "AND RIS.DSHSJ < TO_DATE (\n"
            + "#{endTime},\n"
            + "'yyyy-mm-dd HH24:MI:SS'\n"
            + ")")
    List<ReportTable> findRepotrTableByTime(HealthCard healthCard);
    /**
     * 查询系统时间
     */
    @Select("SELECT SYSDATE -1/24/60 as startTime,SYSDATE as endTime FROM dual")

    HealthCard findTime();
}
